package database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StoredTable extends Table {

	private static final String STORED_INSERT = "INSERT INTO Stored VALUES ('%s', %s, %s)";
	private static final String STORED_DELETE = "DELETE FROM Stored WHERE sname='%s' AND upc=%s";
	private static final String STORED_DISPLAY = "SELECT * FROM Stored";
	
	private static final String STORED_STOCK_QUERY = "SELECT * FROM Stored WHERE sname='%s' AND upc = %s";
	private static final String STORED_STOCK_UPDATE = "UPDATE Stored SET stock = %s WHERE sname ='%s' AND upc = %s";
	
	private static final String STORED_EXISTS_QUERY = "SELECT * FROM stored WHERE sname='%s' AND upc=%s";
	
	public enum Column
	{
		SNAME(1, true),
		UPC(2, true),
		STOCK(3, false);
		
		private int index;
		private boolean isPrimaryKey;
		
		private String[] columnNames = {
				"sName",
				"upc",
				"stock"};
		
		private Column(int index, boolean isPrimaryKey)  
		{
			this.index = index;
			this.isPrimaryKey = isPrimaryKey;			
		}
		
		public int getIndex()
		{
			return index;
		}
		
		public String toString()
		{
			return columnNames[this.ordinal()];
		}

		public boolean isPrimaryKey() {
			return this.isPrimaryKey;
		}
	};
	
	public StoredTable()
	{
		super(STORED_INSERT, STORED_DELETE, STORED_DISPLAY, STORED_EXISTS_QUERY);
	}	

	@Override
	public List<String> getPrimaryKeyValues(List<String> insertValues) {
		List<String> primaryKeyValues = new ArrayList<String>();
		for(Column col : Column.values())
		{
			if(col.isPrimaryKey())
			{	
				primaryKeyValues.add(insertValues.get(col.ordinal()));
			}
		}				
		
		return primaryKeyValues;
	}
	
	@Override
	public List<String> getColumnNames() {
		Column columns[] = Column.values();
		
		List<String> columnNames = new ArrayList<String>(columns.length);
		for( Column col : columns)
		{
			columnNames.add(col.ordinal(), col.toString());
		}
		return columnNames;
	}

	@Override
	public List<String> getPrimaryKeyColumnNames() {
		Column columns[] = Column.values();
		
		List<String> primaryKeyColumnNames = new ArrayList<String>(columns.length);
		for( Column col : columns)
		{
			if(col.isPrimaryKey())
			{
				primaryKeyColumnNames.add(col.ordinal(), col.toString());
			}
		}
		return primaryKeyColumnNames;
	}
	
	public int getStockStored(String storeName, String upc) throws SQLException
	{
		int quantity = 0;
		
		String fmtdQuery = String.format(STORED_STOCK_QUERY, storeName, upc);
		ResultSet rs = dbCon.executeQuery(fmtdQuery);
		if(rs != null)
		{
			if(rs.next())
			{
				quantity = rs.getInt(Column.STOCK.getIndex());		
			}
			
			rs.close();
		}
	
		return quantity;		
	}
	
	public void updateStock(String storeName, String upc, int newStock) throws SQLException {
		String fmtdQuery = String.format(STORED_STOCK_UPDATE, newStock, storeName, upc);
		dbCon.executeStatement(fmtdQuery);		
	}
	
	public boolean checkIfStoredEntryExists(String sName, String upc) throws SQLException
	{
		boolean entryExists = false;
		
		String fmtdQuery = String.format(STORED_EXISTS_QUERY, sName, upc);
		ResultSet rs = dbCon.executeQuery(fmtdQuery);
		
		if(rs != null)
		{
			entryExists = rs.next();
		}
		
		rs.close();
		
		return entryExists;
	}
	
	public void insertStoredEntry(String sName, String upc, int stock) throws SQLException
	{
		String fmtdStatement = String.format(STORED_INSERT, sName, upc, String.valueOf(stock));
		dbCon.executeStatement(fmtdStatement);
	}
}
